<?php

    class ContractUtil
    {

        public static function getContractTypeList()
        {
            return $typeList = Yii::app()->db->createCommand('select  * from tbl_contract_type order by sort')->queryAll();
        }

        public static function getContractTypeCountStatic()
        {
            $typeList               = self::getContractTypeList();
            $result                 = array();
            $countCommand           = Yii::app()->db->createCommand();
            $countCommand->select   = 'count(*)';
            $countCommand->from     = 'tbl_contracts';
            $countCommand->where    = 'contractype=:type';
            foreach ($typeList as $contractType)
            {
                $count = $countCommand->bindValue('type', $contractType['id'])->queryScalar();
                $result[$contractType['description']] = $count;
            }
            return $result;
        }

        public static function getContractTypeExpiredStatic()
        {
            $typeList               = self::getContractTypeList();
            $result                 = array();
            $countCommand           = Yii::app()->db->createCommand();
            $countCommand->select   = 'count(*)';
            $countCommand->from     = 'tbl_contracts c1';
            $countCommand->where    = 'c1.contractype=:type';
            $countCommand->join     = 'left join tbl_contracts c2 on c2.orginalcontractid = c1.contractid ';
            $countCommand->where    .=' 
                and datediff(now(),c1.contractenddate) >=-45
                and c2.contractid is null';
            foreach ($typeList as $contractType)
            {
                $count = $countCommand->bindValue('type', $contractType['id'])->queryScalar();
                $result[$contractType['description']] = $count;
            }
            return $result;
        }

        public static function getContractStatusStatic($contractType, $showExpired = true)
        {
            $statusList = Yii::app()
                    ->db
                    ->createCommand('select * from tbl_contract_status order by sort')
                    ->queryAll();
            $result = array();
            $countCommand = Yii::app()
                    ->db
                    ->createCommand();
            $countCommand->select   = 'count(*)';
            $countCommand->from     = 'tbl_contracts c1';



            $countCommand->where = '
                    c1.status=:status 
                    and c1.contractype=:type ';
            

            $countCommand->join     = 'left join tbl_contracts c2 on c2.orginalcontractid = c1.contractid ';
            $countCommand->where    .= ' and datediff(now(),c1.contractenddate) >=-45
                    and c2.contractid is null';

            $countCommand->bindValue('type', $contractType);
            foreach ($statusList as $status)
            {
                $count                          = $countCommand->bindValue('status', $status['id'])->queryScalar();
                $result[$status['description']] = $count;
            }
            return $result;
        }

        public static function getContractBillingStatusStatic($contractType = null, $showExpired = true)
        {
            $billingStatusList  = self::getContractBillingStatusList();
            $command            = Yii::app()->db->createCommand();
            $command->select    = 'count(*)';
            $command->from      = 'tbl_contracts c1';


            $command->where = '
                c1.billingstatus=:billingstatus and 
                c1.contractype=:type and
                c1.status = :status
                ';
            

            $command->join  = 'left join tbl_contracts c2 on c1.contractid= c2.orginalcontractid';
            $command->where .=' 
                    and datediff(now(),c1.contractenddate) >=-45
                    and c2.contractid is null';
            $command->bindValue('status', 5);
            $command->bindValue('type', $contractType);

            $rs = array();
            foreach ($billingStatusList as $item)
            {
                $id                 = $item['id'];
                $count              = $command->bindValue('billingstatus', $id)->queryScalar();
                $rs[$item['name']]  = $count;
            }
            return $rs;
        }

        public static function getContractBillingStatusList()
        {
            $statusList = Yii::app()->db->createCommand('select * from tbl_dropdowns where type="C-BS"')->queryAll();
            return $statusList;
        }

        public static function getContractExpireStatic()
        {
            //select all contract with expire day
            $expireDayList = array(
                1, 10, 12, 15, 30, 45, 60, 90
            );
            $sql = 'select count(*) from tbl_contracts where datediff(now(),contractenddate) <:max and datediff(now(),contractenddate)>=:min';

            $command = Yii::app()->db->createCommand($sql);
            $result = array();
            $lastNumber = 0;
            foreach ($expireDayList as $day)
            {
                $count = $command->bindValues(array(
                            'max' => $day,
                            'min' => $lastNumber
                        ))->queryScalar();
                $result[$day]   = $count;
                $lastNumber     = $day;
            }
            return array_reverse($result, true);
        }

        public static function calculateRemainingDay(Contracts $contract)
        {
            $enddate = CDateTimeParser::parse($contract->contractenddate, 'yyyy-MM-dd');
            $diff = $enddate - mktime(0, 0, 0, date('n'), date('j'), date('Y'));
            if ($diff > 0)
                return floor($diff / 86400);
            else
                return 0;
        }

        public static function findRenewContract(Contracts $contract)
        {

            $renewContract = Contracts::model()->find('orginalcontractid=:contractId', array(
                'contractId' => $contract->contractid
                    ));

            if ($renewContract !== false)
            {
                return $renewContract;
            }
            else
            {
                return false;
            }
        }

        public static function canRenewal(Contracts $contract)
        {
            return (self::findRenewContract($contract) == null && self::calculateRemainingDay($contract) <= 45);
        }

        public static function copyEquipment(Contracts $contract1, Contracts $contract2)
        {
            $equipments             = Yii::app()->db->createCommand('select * from tbl_contract_equipment where contractid=:contractid')->bindValue('contractid', $contract1->contractid)->queryAll();
            $updateEquipmentSergno  = Yii::app()->db->createCommand('update tbl_equipment set seragrno=:contractid where id=:id');
            if ($equipments != null)
            {
                for ($i = 0; $i < count($equipments); $i++)
                {
                    $equipment                      = $equipments[$i];
                    $id                             = $equipments[$i]['id'];
                    $equipments[$i]['contractid']   = $contract2->contractid;
                    unset($equipments[$i]['id']);
                    $rowEffected = Yii::app()->db->createCommand()->insert('tbl_contract_equipment', $equipments[$i]);
                    if ($rowEffected > 0)
                    {
                        $updateEquipmentSergno->bindValues(array(
                            'contractid'    => $contract2->contractid,
                            'id'            => $id
                        ))->query();
                    }
                }
            }

            if($contract1->contractype == 4){
                $softwareOptions = Yii::app()
                                    ->db
                                    ->createCommand('select * from tbl_contract_sofware_maint_ledger where contractid=:contractid')
                                    ->bindValue('contractid',$contract1->contractid)
                                    ->queryAll();

                foreach($softwareOptions as $software){
                    unset($software['id']);
                    $software['contractid'] = $contract2->contractid;
                    Yii::app()->db->createCommand()->insert('tbl_contract_sofware_maint_ledger',$software);
                }                                    
            }
        }

        public static function getContractListByLocation($custno,$locno){
            $contractList= Contracts::model()->findAll('custno=:custno and locno=:locno',array(
                'custno'    => $custno,
                'locno'     => $locno
            ));
            return $contractList;
        }

    }

?>
